const log4js = require('log4js');
const logger = log4js.getLogger('ipc');
const config = require('../config');
const { Pool } = require('pg');

const pool = new Pool(config.db);
pool.on('error', (err) => {
    logger.error('Unexpected error on idle client', err);
})

const DataProcess = {
    async saveBatch (body, options) {
        delete body['db'];
        delete body['table'];
        const keys = Object.keys(body);
        
        if (!keys.length) {
            logger.warn('传输过来的数据为空')
            return 1;
        } else {
            logger.info('数据行数：', keys.length);

            let client = await pool.connect();
            logger.info('pool: ', pool.waitingCount, pool.idleCount, pool.totalCount);
            let success = false;
            try {
                const {db, table, columns} = options;
                let sql = `insert into ${db}.${table} (`;
                let valueSql = [];
                const cols = columns.split(',');
                for (let i in cols) {
                    sql += cols[i] + ',';
                    valueSql.push('?');
                }
                sql = sql.substr(0, sql.length - 1);
                sql += ') (SELECT * FROM UNNEST (';

                const values = [];
                const unnestSql = DataProcess.buildValues(values, body, keys, cols);
                sql += unnestSql.join(',') +'))'
                
                const queryRet = await client.query(sql, values);

                logger.debug('sql:', sql, values, queryRet);
                success = true;
            } catch (e) {
                // 回滚
                logger.error('出现错误', e);
            } finally {
                await client.release(true);
                logger.info('release to pool: ', pool.waitingCount, pool.idleCount, pool.totalCount);
            }
            logger.info('数据录完');
            if (success) {
                return 1
            } else {
                return -1;
            }
        }
    },

    /**
     * 更新或插入
     * @param {*} body 
     * @param {*} options 
     * @returns 
     */
    async updateBatch  (body, options) {
        delete body['db'];
        delete body['table'];
        const keys = Object.keys(body);
        
        if (!keys.length) {
            logger.warn('传输过来的数据为空')
            return 1;
        } else {
            logger.info('数据行数：', keys.length);

            let client = await pool.connect();
            let success = false;
            try {
                const {db, table, columns, primaryField} = options;
                let sql = `insert into ${db}.${table} (`;
                const cols = columns.split(',');
                sql += cols.join(',');
                sql += ') values ';

                for (let i = 0; i < keys.length; i++) {
                    const row = body[keys[i]];
                    if (row) {
                        const querySql = `select node_id, _seq_no, _primary_term from ${db}.${table} where ${primaryField}=?`;
                        const ret = await client.query(querySql, [row[primaryField]]);
                        if (ret.rows.length === 0) {
                            let uSql = DataProcess.updateLine(row, cols, i);
                            let insertOrUpdateSql = sql + uSql;
                            logger.debug('sql:', insertOrUpdateSql);
                            await client.query(insertOrUpdateSql);
                        } else {
                            const node = ret.rows[0];
                            let updateSql = `update ${db}.${table} set `;
                            const uSql = DataProcess.updateLine2(primaryField, row, cols, i);
                            updateSql += uSql + ` where ${primaryField}='${row[primaryField]}' and "_seq_no" = ${node._seq_no} and "_primary_term"=${node._primary_term}`;
                            logger.debug('sql:', updateSql);
                            await client.query(updateSql);
                        }
                    }
                }
                success = true;
            } catch (e) {
                logger.error('出现错误', e);
            } finally {
                await client.release(true);
            }
            logger.info('数据录完');
            if (success) {
                return 1
            } else {
                return -1;
            }
        }
    },

    /**
     * 构建uneset表
     * @param {*} values 
     * @param {*} body 
     * @param {*} keys 
     * @param {*} cols 
     */
    buildValues (values, body, keys, cols) {
        const filedSql = [];
        for (let i in cols) {
            const fieldVals = [];
            // const vals = [];
            const field = cols[i].trim();
            for (let j = 0, l = keys.length; j < l; j++) {
                const row = body[keys[j]];
                const val = row[field];
                fieldVals.push('?');
                values.push(val);
                // if (typeof val === 'string') {
                //     fieldVals.push(`'${val}'`);
                // } else if (val === null || val === undefined) {
                //     fieldVals.push('NULL');
                // } else {
                //     fieldVals.push(val);
                // }
            }
            // values.push(vals);
            filedSql.push('[' + fieldVals.join(',') + ']');
        }
        return filedSql;
    },

    /**
     * 一行一行保存记录
     * @param {*} line 
     * @param {*} index 第几行
     */
    saveLine (line, cols, index) {
        if (line) {
            logger.debug('数据 ', index, ' : ', line);
            const values = [];
            for (let i in cols) {
                const field = cols[i].trim();
                const val = line[field];
                values.push(val);
            }
            
            return values;
        }
    },

    updateLine (line, cols, index) {
        let sql = '';
        if (line) {
            logger.debug('数据 ', index, ' : ', line);
            const values = [];
            for (let i in cols) {
                const field = cols[i].trim();
                const val = line[field];
                if (typeof val === 'string') {
                    values.push(`'${val}'`);
                } else if (val === null || val === undefined) {
                    values.push('NULL');
                } else {
                    values.push(val);
                }
            }
            
            sql = `(${values.join(',')})`;
        }
        return sql;
    },

    /**
     * update set
     * @param {*} params 
     * @param {*} line 
     * @param {*} cols 
     * @returns 
     */
    updateLine2 (primaryField, line, cols) {
        let sql = '';
        if (line) {
            const values = [];
            for (let i in cols) {
                const field = cols[i].trim();
                if (primaryField !== field) {
                    const val = line[field];
                    if (typeof val === 'string') {
                        values.push(`${field}='${val}'`);
                    } else if (val === null || val === undefined) {
                        values.push(field + '=NULL');
                    } else {
                        values.push(field + '=' + val);
                    }
                }
            }
            
            sql = `${values.join(',')}`;
        }
        return sql;
    }
}

module.exports = DataProcess;